---------- [Documents_Search]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Documents_Search]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Documents_Search]
GO

CREATE PROCEDURE [dbo].[Documents_Search]
	@agent_firstname varchar(100),
	@agent_lastname varchar(100),
	@from datetime,
	@to datetime,
	@documentClassId int
AS
	DECLARE @sysuser VARCHAR(100)
	SELECT @sysuser = dbo.GetCurrentUser();
	--SELECT @sysuser = 'julianh';

	-- create a temporary table to hold the results
	DECLARE @tmpPermittedAgents TABLE (logonname VARCHAR(100), FIRST_NAME VARCHAR(100), LAST_NAME VARCHAR(100), Name VARCHAR(100), Department VARCHAR(100), OBJECT_GUID UNIQUEIDENTIFIER)
	INSERT INTO @tmpPermittedAgents EXEC CDC_QRD.[dbo].GetPermittedAgents @sysuser;

	DECLARE @agent_firstname_regex varchar(102);
	select 	@agent_firstname_regex = '%' + @agent_firstname + '%';

	DECLARE @agent_lastname_regex varchar(102);
	select 	@agent_lastname_regex = '%' + @agent_lastname + '%';

	select 
		*
	from 
		Socrates.dbo.docuPrintQ 
	inner join 
		@tmpPermittedAgents tpa on tpa.logonname = Socrates.dbo.docuPrintQ.RequestBy collate SQL_Latin1_General_CP1_CI_AS
	inner join 
		Socrates.dbo.docuDocClasses on docuDocClasses.DocClassID = docuPrintQ.DocClassID
	where 
		(@documentClassId is null or docuDocClasses.DocClassID = @documentClassId) AND
		(@agent_firstname is null or '' = @agent_firstname or tpa.FIRST_NAME like @agent_firstname_regex) AND
		(@agent_lastname is null or '' = @agent_lastname or tpa.LAST_NAME like @agent_lastname_regex) AND
		(@from is null or docuPrintQ.RequestDate >= @from) AND 
		(@to is null or docuPrintQ.RequestDate <= @to) AND
		DocId is not null	

GO

-- EXEC [dbo].[Documents_Search] null, null, null, null, null


EXEC Documents_Search 'rhonda', null, '2012-05-24', null, 2500



-- executing user
--	DECLARE @sysuser VARCHAR(100)
--	SELECT @sysuser = CDC_QRD.dbo.GetCurrentUser();

--	-- create a temporary table to hold the results
--	DECLARE @tmpPermittedAgents TABLE (logonname VARCHAR(100), FIRST_NAME VARCHAR(100), LAST_NAME VARCHAR(100), Name VARCHAR(100), Department VARCHAR(100), OBJECT_GUID UNIQUEIDENTIFIER)
--	INSERT INTO @tmpPermittedAgents EXEC CDC_QRD.[dbo].GetPermittedAgents 'julianh';

--select * from  @tmpPermittedAgents tpa

--select 
--	* 
--from 
--	docuPrintQ 
--inner join 
--	@tmpPermittedAgents tpa on tpa.logonname = docuPrintQ.RequestBy 
--inner join 
--	docuDocClasses on docuDocClasses.DocClassID = docuPrintQ.DocClassID
--where 
--	docuDocClasses.DocClassID = 2500
		
